package com.gamatechno.solodestination.model;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {
	private static final int DB_VERSION = 4;
	private static final String DATABASE_NAME = "solodestination";
	private static final String TABLE_EVENT = "event";
	private static final String TABLE_FAV = "favorite";

	private static final String KEY_EVENT_ID = "id_event";
	private static final String KEY_EVENT_NAME = "name";
	private static final String KEY_EVENT_DESC = "descripriton";
	private static final String KEY_EVENT_LAT = "lat";
	private static final String KEY_EVENT_LON = "lon";
	private static final String KEY_EVENT_START = "startDate";
	private static final String KEY_EVENT_END = "endDate";
	private static final String KEY_EVENT_IMG = "images";
	private static final String KEY_EVENT_CATEGORY = "category";
	private static final String KEY_EVENT_DISTRICT_ID = "district_id";
	private static final String KEY_EVENT_DISTRICT = "district";

	private static final String KEY_FAV_ID = "id_favorite";
	private static final String KEY_FAV_NAME = "name";
	private static final String KEY_FAV_CAT = "category";
	private static final String KEY_FAV_RATE = "rate";
	private static final String KEY_FAV_COMM = "comments";
	private static final String KEY_FAV_IMG = "img";

	public DBHandler(Context context) {
		super(context, DATABASE_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.i("db", "created db");
		String query_table_event = "CREATE TABLE " + TABLE_EVENT + "("
				+ KEY_EVENT_ID + " INTEGER PRIMARY KEY, " + KEY_EVENT_NAME
				+ " TEXT, " + KEY_EVENT_DESC + " TEXT, " + KEY_EVENT_LAT
				+ " TEXT, " + KEY_EVENT_LON + " TEXT, " + KEY_EVENT_START
				+ " TEXT, " + KEY_EVENT_END + " TEXT, " + KEY_EVENT_IMG
				+ " TEXT, " + KEY_EVENT_CATEGORY + " TEXT, "
				+ KEY_EVENT_DISTRICT_ID + " INTEGER, " + KEY_EVENT_DISTRICT
				+ " TEXT " + ")";
		String query_table_favorite = "CREATE TABLE " + TABLE_FAV + "("
				+ KEY_FAV_ID + " INTEGER PRIMARY KEY, " + KEY_FAV_NAME
				+ " TEXT, " + KEY_FAV_CAT + " TEXT, " + KEY_FAV_IMG + " TEXT, "
				+ KEY_FAV_RATE + " DOUBLE, " + KEY_FAV_COMM + " TEXT " + ")";
		Log.i("db", "query teble event : " + query_table_event);
		db.execSQL(query_table_event);
		db.execSQL(query_table_favorite);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENT);
		onCreate(db);
	}

	public void addEvent(Event event) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_EVENT_ID, event.getId());
		values.put(KEY_EVENT_NAME, event.getName());
		values.put(KEY_EVENT_DESC, event.getDescription());
		values.put(KEY_EVENT_LAT, event.getLat());
		values.put(KEY_EVENT_LON, event.getLon());
		values.put(KEY_EVENT_START, event.getStartDate());
		values.put(KEY_EVENT_END, event.getEndDate());
		values.put(KEY_EVENT_IMG, event.getImages());
		values.put(KEY_EVENT_CATEGORY, event.getCategory());
		values.put(KEY_EVENT_DISTRICT_ID, event.getDistrict_id());
		values.put(KEY_EVENT_DISTRICT, event.getDistrict());
		Log.i("db", "inserting event...");
		db.insert(TABLE_EVENT, null, values);
		db.close();
	}

	public void addFav(Favorite favorite) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_FAV_ID, favorite.getId());
		values.put(KEY_FAV_NAME, favorite.getName());
		values.put(KEY_FAV_CAT, favorite.getCategory());
		values.put(KEY_FAV_IMG, favorite.getImg());
		values.put(KEY_FAV_RATE, favorite.getRate());
		values.put(KEY_FAV_COMM, favorite.getRate());
		db.insert(TABLE_FAV, null, values);
		Log.i("db", "inserting favorite..");
		db.close();

	}

	public boolean cekFav(String id) {
		String selectQuery = "SELECT " + KEY_FAV_ID + " FROM " + TABLE_FAV
				+ " WHERE " + KEY_FAV_ID + "='" + id + "'";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(selectQuery, null);
		int i = 0;
		if (cur.moveToFirst()) {
			do {
				i++;
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		if (i > 0)
			return true;
		else
			return false;
	}

	public List<Event> getEventListByDate(String date) {
		List<Event> event = new ArrayList<Event>();
		String selectQuery = "SELECT " + KEY_EVENT_ID + ", " + KEY_EVENT_START
				+ ", " + KEY_EVENT_END + ", " + KEY_EVENT_NAME + " FROM "
				+ TABLE_EVENT + " WHERE " + KEY_EVENT_START + " LIKE'" + date
				+ "%'";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		if (c.moveToFirst()) {
			do {
				Event e = new Event();
				e.setId(Integer.parseInt(c.getString(c
						.getColumnIndex(KEY_EVENT_ID))));
				e.setStartDate(c.getString(c.getColumnIndex(KEY_EVENT_START)));
				e.setEndDate(c.getString(c.getColumnIndex(KEY_EVENT_END)));
				e.setName(c.getString(c.getColumnIndex(KEY_EVENT_NAME)));
				event.add(e);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return event;
	}

	public List<Event> getEventList() {
		List<Event> event = new ArrayList<Event>();
		String selectQuery = "SELECT " + KEY_EVENT_ID + ", " + KEY_EVENT_START
				+ ", " + KEY_EVENT_END + ", " + KEY_EVENT_NAME + " FROM "
				+ TABLE_EVENT;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		if (c.moveToFirst()) {
			do {
				Event e = new Event();
				e.setId(Integer.parseInt(c.getString(c
						.getColumnIndex(KEY_EVENT_ID))));
				e.setStartDate(c.getString(c.getColumnIndex(KEY_EVENT_START)));
				e.setEndDate(c.getString(c.getColumnIndex(KEY_EVENT_END)));
				e.setName(c.getString(c.getColumnIndex(KEY_EVENT_NAME)));
				event.add(e);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return event;
	}

	public List<Favorite> getFavorite() {
		List<Favorite> favorite = new ArrayList<Favorite>();
		String selectQuery = "SELECT " + KEY_FAV_ID + ", " + KEY_FAV_NAME
				+ ", " + KEY_FAV_CAT + ", " + KEY_FAV_IMG + ", " + KEY_FAV_RATE
				+ ", " + KEY_FAV_COMM + " FROM " + TABLE_FAV;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		if (c.moveToFirst()) {
			do {
				Favorite f = new Favorite();
				f.setId(c.getString(c.getColumnIndex(KEY_FAV_ID)));
				f.setName(c.getString(c.getColumnIndex(KEY_FAV_NAME)));
				f.setImg(c.getString(c.getColumnIndex(KEY_FAV_IMG)));
				f.setRate(c.getDouble(c.getColumnIndex(KEY_FAV_RATE)));
				f.setComments(c.getString(c.getColumnIndex(KEY_FAV_COMM)));
				f.setCategory(c.getString(c.getColumnIndex(KEY_FAV_CAT)));
				favorite.add(f);
			} while (c.moveToNext());
		}
		c.close();
		db.close();
		return favorite;
	}

	public boolean cekDateHasEvent(String date) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT " + KEY_EVENT_START + " FROM "
				+ TABLE_EVENT + " WHERE " + KEY_EVENT_START + " LIKE'" + date
				+ "%'", null);
		int i = 0;
		if (cur.moveToFirst()) {
			do {
				i++;
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		if (i > 0)
			return true;
		else
			return false;

	}

	public Event getEventById(String id) {
		Event e = new Event();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT * FROM " + TABLE_EVENT + " WHERE "
				+ KEY_EVENT_ID + "='" + id + "'", null);

		if (cur != null)
			cur.moveToFirst();
		e.setId(Integer.parseInt(cur.getString(cur.getColumnIndex(KEY_EVENT_ID))));
		e.setName(cur.getString(cur.getColumnIndex(KEY_EVENT_NAME)));
		e.setDescription(cur.getString(cur.getColumnIndex(KEY_EVENT_DESC)));
		e.setLat(cur.getString(cur.getColumnIndex(KEY_EVENT_LAT)));
		e.setLon(cur.getString(cur.getColumnIndex(KEY_EVENT_LON)));
		e.setStartDate(cur.getString(cur.getColumnIndex(KEY_EVENT_START)));
		e.setEndDate(cur.getString(cur.getColumnIndex(KEY_EVENT_END)));
		e.setImages(cur.getString(cur.getColumnIndex(KEY_EVENT_IMG)));
		e.setCategory(cur.getString(cur.getColumnIndex(KEY_EVENT_CATEGORY)));
		e.setDistrict_id(Integer.parseInt(cur.getString(cur
				.getColumnIndex(KEY_EVENT_DISTRICT_ID))));
		e.setDistrict(cur.getString(cur.getColumnIndex(KEY_EVENT_DISTRICT)));
		cur.close();
		db.close();
		return e;
	}

	public boolean cekEvent() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT " + KEY_EVENT_ID + " FROM "
				+ TABLE_EVENT, null);
		int i = 0;
		if (cur.moveToFirst()) {
			do {
				i++;
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		if (i > 0)
			return true;
		else
			return false;
	}

	public void delFav(String id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "DELETE FROM " + TABLE_FAV + " WHERE " + KEY_FAV_ID + "='"
				+ id + "'";
		db.execSQL(str);
		db.close();
	}

}
